from sqlalchemy import text
from sqlalchemy.orm import Session

from core.entity.do.models import CmsCar
from core.entity.vo.view_models import CarMinitorObjectForQuery
from utils.time_format_util import object_format_datetime


class CmsCarDao:

    @classmethod
    def get_cms_car_like_car_number(cls, db: Session, car_number: str):
        """
        根据车牌号模糊查询车辆列表
        :param db: orm对象
        :param fence_name: 终端名称
        :return: 高德终端信息对象集合
        """
        cms_car_list = db.query(CmsCar) \
            .filter(CmsCar.car_number.like(f'%{car_number}%') if car_number else True) \
            .all()

        return cms_car_list

    @classmethod
    def get_cms_car_list(cls, db: Session, car: CarMinitorObjectForQuery):
        """
        根据车牌号模糊查询车辆列表并支持分页
        :param db: orm对象
        :param offset: 分页偏移量
        :param limit: 每页数据量
        :return: 车辆列表信息对象
        """
        # todo 实时查询运行多少距离
        sql_text = """
        SELECT tb1.car_id, tb1.device_id, tb1.car_number, 
            CASE WHEN EXTRACT(EPOCH FROM (NOW() - a.up_time))<=3*60 THEN '在线' ELSE '离线' END AS state, 
            tb4.order_id, tb4.use_car_type, tb4.driver_id, tb5.driver_name, tb4.start, tb4.destination,  
            0.0 as distance, a.lon, a.lat,
                    CASE WHEN tb4.reality_end_time is not null 
                    THEN ROUND(EXTRACT(EPOCH FROM (tb4.reality_end_time - tb4.reality_begin_time))/3600,2)
                    WHEN tb4.reality_begin_time is not null 
                    THEN ROUND(EXTRACT(EPOCH FROM (NOW() - tb4.reality_begin_time))/3600,2)
                    ELSE 0 
                    END as time
            FROM cms_car tb1 
            LEFT JOIN (SELECT tb2.order_id, tb3.driver_id, tb3.car_id, tb2.use_car_type, tb2.start, tb2.destination, 
            tb3.id, tb3.reality_begin_time, tb3.reality_end_time 
                FROM (SELECT * FROM cms_order WHERE state = 6) tb2 
                LEFT JOIN cms_order_execute tb3 ON tb2.order_id = tb3.order_id) tb4  
            ON tb1.car_id = tb4.car_id  
            LEFT JOIN cms_driver tb5 ON tb4.driver_id = tb5.driver_id  
            LEFT join (
                    SELECT
                    DISTINCT ON (device_id) device_id,
                    up_time,
                    CAST(ST_X(transformed_geom) AS numeric) AS lon,  
                    CAST(ST_Y(transformed_geom) AS numeric) AS lat  
                    FROM (  
                    SELECT
                    device_id,
                    up_time,
                    geoc_wgs84togcj02_point(ST_SetSRID(ST_MakePoint(lon::numeric, lat::numeric), 4326)) AS transformed_geom
                    from cms_terminal_point
                    ) AS subquery
                    where up_time > NOW() - INTERVAL '3 minute' ORDER BY device_id, up_time DESC
                    ) a on tb1.device_id=a.device_id 
            where 1=1 
        """
        if car.car_number:
            sql_text = sql_text + f"and tb1.car_number = '{car.car_number}' "
        if car.car_number_list:
            car_numbers_placeholders = ', '.join(f"'{item}'" for item in car.car_number_list)
            sql_text += f" AND tb1.car_number IN ({car_numbers_placeholders})"
        if car.state:
            sql_text = sql_text + (f"and CASE WHEN EXTRACT(EPOCH FROM (NOW() - a.up_time))<=3*60 THEN '在线' "
                                   f"ELSE '离线' END = '{car.state}' ")
        sql_text += "ORDER BY tb1.create_time "
        sql = text(
            sql_text
        )

        cms_car_list = db.execute(sql).all()
        return cms_car_list

    @classmethod
    def get_cms_car_list_all(cls, db: Session):
        """
        根据车牌号模糊查询车辆列表并支持分页
        :param db: orm对象
        :return: 车辆列表信息对象
        """
        sql = text(
            "SELECT tb1.car_id, tb1.device_id, tb1.car_number, tb1.gaode_tid as tid, ter.fence_id, fen.alarm_type, "
            "fen.fence_name, fen.polygon_points, "
            "CASE WHEN tb4.order_id IS NOT NULL THEN 'online' ELSE 'offline' END AS state, "
            "tb4.order_id, tb4.use_car_type, tb4.driver_id, tb5.driver_name, tb4.start, tb4.destination, "
            "tb4.gaode_track_id "
            "FROM cms_car tb1 "
            "INNER JOIN cms_gaode_fence_terminal ter ON tb1.car_id = ter.terminal_id "
            "INNER JOIN cms_gaode_fence fen ON fen.fence_id = ter.fence_id "
            "INNER JOIN (SELECT tb2.order_id, tb3.driver_id, tb3.car_id, tb3.gaode_track_id, "
            "tb2.use_car_type, tb2.start, tb2.destination "
            "           FROM (SELECT * FROM cms_order WHERE state = 6) tb2 "
            "           LEFT JOIN cms_order_execute tb3 ON tb2.order_id = tb3.order_id) tb4 "
            "ON tb1.car_id = tb4.car_id "
            "LEFT JOIN cms_driver tb5 ON tb4.driver_id = tb5.driver_id "
        )

        cms_car_list = db.execute(sql).all()
        return cms_car_list

    @classmethod
    def get_cms_car_list_all_track(cls, db: Session):
        #todo 坐标转换 可以不写
        sql = text(
            "select c.car_id,c.device_id,c.car_number,a.lon,a.lat,0, "
            "0,0,trace.execute_id,d.driver_id, d.driver_name, "
            "trace.service_id,t.terminal_id "
            "from cms_gaode_trace trace "
            "inner join cms_order_execute e on CAST(e.id AS TEXT) = trace.execute_id "
            "inner join cms_car c on c.car_id = e.car_id "
            "inner join cms_driver d on d.driver_id = e.driver_id "
            "inner join cms_gaode_terminal t on t.car_id = c.car_id  "
            "inner join cms_terminal_point_new a on c.device_id=a.device_id "
        )
        cms_car_list = db.execute(sql).all()
        return cms_car_list

    @classmethod
    def update_car_by_car_id(cls, db: Session, car_id: str, gaode_tid: str):
        first: CmsCar = db.query(CmsCar).filter(CmsCar.car_id == car_id).first()
        first.gaode_tid = gaode_tid
        db.query(CmsCar) \
            .filter(CmsCar.id == car_id) \
            .update(first)
        db.commit()

    @classmethod
    def get_car_by_car_id(cls, db: Session, car_id: str):
        first = db.query(CmsCar).filter(CmsCar.car_id == car_id).first()
        return object_format_datetime(first)

